#!/usr/bin/env python
# -*- coding: utf-8 -*-

from datetime import datetime
from typing import List
from sqlalchemy import and_, text

from web.constants.datetime_format import DatetimeFormat
from web.manager.oracle_manager import OracleManager
from web.models.etf_transaction_data import EtfTransactionData
from web.util.datetime_util import DatetimeUtil

class EtfTransactionDataDao:
    """
    EtfTransactionData的dao类
    """

    def __init__(self):
        self.db = OracleManager()

    def find_date_and_close_price_by_code_between_date_order_by_date_asc(self, code, begin_date, end_date):
        """
        在一个时间范围内，查询某个ETF，并按照日期升序排列
        """

        self.db.connect()
        _list = self.db.execute_query("select t.date_, t.close_price from etf_transaction_data t where t.code_='" + code + "' "
                                         "and t.date_ between to_date(" + begin_date + ", 'yyyy-mm-dd') "
                                         "and to_date(" + end_date + ", 'yyyy-mm-dd') "
                                         "order by t.date_ asc")
        self.db.close()
        date_and_close_price_list = list()
        for model in _list:
            date_and_close_price_list.append(model)
        return date_and_close_price_list

    def batch_insert(self, etf_transaction_data_list):
        """
        批量插入记录
        """

        sql = ('insert into etf_transaction_data(DATE_, CODE_, OPEN_PRICE, CLOSE_PRICE, HIGHEST_PRICE, LOWEST_PRICE, '
               'LAST_CLOSE_PRICE, CHANGE_AMOUNT, CHANGE_RANGE, VOLUME, TURNOVER, MA5, MA10, MA20, MA60, MA120, MA250, '
               'HA_OPEN_PRICE, HA_CLOSE_PRICE, HA_HIGHEST_PRICE, HA_LOWEST_PRICE, BIAS5, BIAS10, BIAS20, BIAS60, BIAS120, '
               'BIAS250, EMA12, EMA26, DIF, DEA, MACD, RSV, K, D, CORRELATION5_WITH000001, CORRELATION5_WITH399001, '
               'CORRELATION5_WITH399005, CORRELATION5_WITH399006, CORRELATION5_WITH_AVG_C_P, CORRELATION250_WITH_AVG_C_P) '
               'values(:DATE_, :CODE_, :OPEN_PRICE, :CLOSE_PRICE, :HIGHEST_PRICE, :LOWEST_PRICE, '
               ':LAST_CLOSE_PRICE, :CHANGE_AMOUNT, :CHANGE_RANGE, :VOLUME, :TURNOVER, :MA5, :MA10, :MA20, :MA60, :MA120, :MA250, '
               ':HA_OPEN_PRICE, :HA_CLOSE_PRICE, :HA_HIGHEST_PRICE, :HA_LOWEST_PRICE, :BIAS5, :BIAS10, :BIAS20, :BIAS60, :BIAS120, '
               ':BIAS250, :EMA12, :EMA26, :DIF, :DEA, :MACD, :RSV, :K, :D, :CORRELATION5_WITH000001, :CORRELATION5_WITH399001, '
               ':CORRELATION5_WITH399005, :CORRELATION5_WITH399006, :CORRELATION5_WITH_AVG_C_P, :CORRELATION250_WITH_AVG_C_P)')
        param_list = list()
        for etf_transaction_data in etf_transaction_data_list:
            param = (etf_transaction_data.date_, etf_transaction_data.code_, etf_transaction_data.open_price, etf_transaction_data.close_price,
                     etf_transaction_data.highest_price, etf_transaction_data.lowest_price, etf_transaction_data.last_close_price, etf_transaction_data.change_amount,
                    etf_transaction_data.change_range, etf_transaction_data.volume, etf_transaction_data.turnover, etf_transaction_data.ma5, etf_transaction_data.ma10,
                    etf_transaction_data.ma20, etf_transaction_data.ma60, etf_transaction_data.ma120, etf_transaction_data.ma250, etf_transaction_data.ha_open_price,
                    etf_transaction_data.ha_close_price, etf_transaction_data.ha_highest_price, etf_transaction_data.ha_lowest_price, etf_transaction_data.bias5,
                    etf_transaction_data.bias10, etf_transaction_data.bias20, etf_transaction_data.bias60, etf_transaction_data.bias120, etf_transaction_data.bias250,
                     etf_transaction_data.ema12, etf_transaction_data.ema26, etf_transaction_data.dif, etf_transaction_data.dea, etf_transaction_data.macd,
                     etf_transaction_data.rsv, etf_transaction_data.k, etf_transaction_data.d, etf_transaction_data.correlation5_with000001,
                     etf_transaction_data.correlation5_with399001, etf_transaction_data.correlation5_with399005, etf_transaction_data.correlation5_with399006,
                     etf_transaction_data.correlation5_with_avg_c_p, etf_transaction_data.correlation250_with_avg_c_p)
            param_list.append(param)
        self.db.connect()
        self.db.batch_insert(sql, param_list)
        self.db.close()

    def find_for_hmm(self):
        """
        为隐马尔可夫算法收集数据
        """

        self.db.connect()
        _list = self.db.execute_query("select * from etf_transaction_data t where t.code_ not in("
                                         "select distinct t1.code from mdl_hmm t1)")
        self.db.close()
        etf_transaction_data_list = list()
        for model in _list:
            etf_transaction_data = EtfTransactionData()
            etf_transaction_data.code_ = model[2]
            etf_transaction_data.name = model[2]
            etf_transaction_data_list.append(etf_transaction_data)
        return etf_transaction_data_list

    def prepare_for_hmm_with_date_with_many_eigenvalue(self, code: str, begin_date: datetime.date,
                                                       end_date: datetime.date) -> ():
        """
        为hmm算法准备数据
        """

        date_list: List[datetime] = list()
        open_price_list: List[float] = list()
        close_price_list: List[float] = list()
        highest_price_list: List[float] = list()
        lowest_price_list: List[float] = list()
        change_amount_list: List[float] = list()
        change_range_list: List[float] = list()
        volume_list: List[float] = list()
        turnover_list: List[float] = list()
        ma5_list: List[float] = list()
        ma10_list: List[float] = list()
        ma20_list: List[float] = list()
        ma60_list: List[float] = list()
        ma120_list: List[float] = list()
        ma250_list: List[float] = list()
        ha_open_price_list: List[float] = list()
        ha_close_price_list: List[float] = list()
        ha_highest_price_list: List[float] = list()
        ha_lowest_price_list: List[float] = list()
        bias5_list: List[float] = list()
        bias10_list: List[float] = list()
        bias20_list: List[float] = list()
        bias60_list: List[float] = list()
        bias120_list: List[float] = list()
        bias250_list: List[float] = list()
        ema12_list: List[float] = list()
        ema26_list: List[float] = list()
        dif_list: List[float] = list()
        dea_list: List[float] = list()
        rsv_list: List[float] = list()
        k_list: List[float] = list()
        d_list: List[float] = list()
        up_list: List[float] = list()
        mb_list: List[float] = list()
        dn_list: List[float] = list()

        self.db.connect()
        etf_transaction_data_list = self.db.execute_query("select * from etf_transaction_data t where t.code_ = '" + code + "' "
                                         "and t.date_ between to_date(" + begin_date + ", 'yyyy-mm-dd') and to_date(" + end_date + ", 'yyyy-mm-dd') "
                                         "and t.open_price is not null and t.close_price is not null and t.highest_price is not null "
                                         "and t.lowest_price is not null and t.change_amount is not null and t.change_range is not null "
                                         "and t.volume is not null and t.turnover is not null "
                                         "and t.ma5 is not null and t.ma10 is not null and t.ma20 is not null and t.ma60 is not null "
                                         "and t.ma120 is not null and t.ma250 is not null "
                                         "and t.ha_open_price is not null and t.ha_close_price is not null "
                                         "and t.ha_highest_price is not null and t.ha_lowest_price is not null "
                                         "and t.bias5 is not null and t.bias10 is not null and t.bias20 is not null "
                                         "and t.bias60 is not null and t.bias120 is not null and t.bias250 is not null "
                                         "and t.ema12 is not null and t.ema26 is not null "
                                         "and t.dif is not null and t.dea is not null "
                                         "and t.rsv is not null and t.k is not null and t.d is not null "
                                         "order by t.date_ asc")
        self.db.close()
        if etf_transaction_data_list is not None and len(etf_transaction_data_list) > 0:

            for current_etf_transaction_data in etf_transaction_data_list:
                # 查询前一个交易日的记录
                last_etf_transaction_data = self.session.query(EtfTransactionData).filter(
                    and_(EtfTransactionData.code_ == code,
                         EtfTransactionData.date_ < current_etf_transaction_data[1])).order_by(
                    EtfTransactionData.date_.desc()).first()

                # decimal转换为float
                date_list.append(last_etf_transaction_data.date_)
                open_price_list.append(float(last_etf_transaction_data.open_price))
                close_price_list.append(float(last_etf_transaction_data.close_price))
                highest_price_list.append(float(last_etf_transaction_data.highest_price))
                lowest_price_list.append(float(last_etf_transaction_data.lowest_price))
                change_amount_list.append(float(last_etf_transaction_data.change_amount))
                change_range_list.append(float(last_etf_transaction_data.change_range))
                volume_list.append(float(last_etf_transaction_data.volume))
                turnover_list.append(float(last_etf_transaction_data.turnover))
                ma5_list.append(float(last_etf_transaction_data.ma5))
                ma10_list.append(float(last_etf_transaction_data.ma10))
                ma20_list.append(float(last_etf_transaction_data.ma20))
                ma60_list.append(float(last_etf_transaction_data.ma60))
                ma120_list.append(float(last_etf_transaction_data.ma120_))
                ma250_list.append(float(last_etf_transaction_data.ma250))
                ha_open_price_list.append(float(last_etf_transaction_data.ha_open_price))
                ha_close_price_list.append(float(last_etf_transaction_data.ha_close_price))
                ha_highest_price_list.append(float(last_etf_transaction_data.ha_highest_price))
                ha_lowest_price_list.append(float(last_etf_transaction_data.ha_lowest_price))
                bias5_list.append(float(last_etf_transaction_data.bias5))
                bias10_list.append(float(last_etf_transaction_data.bias10))
                bias20_list.append(float(last_etf_transaction_data.bias20))
                bias60_list.append(float(last_etf_transaction_data.bias60))
                bias120_list.append(float(last_etf_transaction_data.bias120))
                bias250_list.append(float(last_etf_transaction_data.bias250))
                ema12_list.append(float(last_etf_transaction_data.ema12))
                ema26_list.append(float(last_etf_transaction_data.ema26))
                dif_list.append(float(last_etf_transaction_data.dif))
                dea_list.append(float(last_etf_transaction_data.dea))
                rsv_list.append(float(last_etf_transaction_data.rsv))
                k_list.append(float(last_etf_transaction_data.k))
                d_list.append(float(last_etf_transaction_data.d))
                up_list.append(float(last_etf_transaction_data.up))
                mb_list.append(float(last_etf_transaction_data.mb))
                dn_list.append(float(last_etf_transaction_data.dn))

        return (date_list,
                open_price_list,
                close_price_list,
                highest_price_list,
                lowest_price_list,
                change_amount_list,
                change_range_list,
                volume_list,
                turnover_list,
                ma5_list,
                ma10_list,
                ma20_list,
                ma60_list,
                ma120_list,
                ma250_list,
                ha_open_price_list,
                ha_close_price_list,
                ha_highest_price_list,
                ha_lowest_price_list,
                bias5_list,
                bias10_list,
                bias20_list,
                bias60_list,
                bias120_list,
                bias250_list,
                ema12_list,
                ema26_list,
                dif_list,
                dea_list,
                rsv_list,
                k_list,
                d_list,
                up_list,
                mb_list,
                dn_list,
                len(etf_transaction_data_list))

    def prepare_for_hmm_with_many_eigenvalue(self, code: str) -> ():
        """
        为hmm算法准备数据
        """

        date_list: List[datetime] = list()
        open_price_list: List[float] = list()
        close_price_list: List[float] = list()
        highest_price_list: List[float] = list()
        lowest_price_list: List[float] = list()
        change_amount_list: List[float] = list()
        change_range_list: List[float] = list()
        volume_list: List[float] = list()
        turnover_list: List[float] = list()
        ma5_list: List[float] = list()
        ma10_list: List[float] = list()
        ma20_list: List[float] = list()
        ma60_list: List[float] = list()
        ma120_list: List[float] = list()
        ma250_list: List[float] = list()
        ha_open_price_list: List[float] = list()
        ha_close_price_list: List[float] = list()
        ha_highest_price_list: List[float] = list()
        ha_lowest_price_list: List[float] = list()
        bias5_list: List[float] = list()
        bias10_list: List[float] = list()
        bias20_list: List[float] = list()
        bias60_list: List[float] = list()
        bias120_list: List[float] = list()
        bias250_list: List[float] = list()
        ema12_list: List[float] = list()
        ema26_list: List[float] = list()
        dif_list: List[float] = list()
        dea_list: List[float] = list()
        rsv_list: List[float] = list()
        k_list: List[float] = list()
        d_list: List[float] = list()
        up_list: List[float] = list()
        mb_list: List[float] = list()
        dn_list: List[float] = list()

        self.db.connect()
        etf_transaction_data_list = self.db.execute_query("select * from etf_transaction_data t where t.code_ = '" + code + "' "
                                         "and t.open_price is not null and t.close_price is not null and t.highest_price is not null "
                                         "and t.lowest_price is not null and t.change_amount is not null and t.change_range is not null "
                                         "and t.volume is not null and t.turnover is not null "
                                         "and t.ma5 is not null and t.ma10 is not null and t.ma20 is not null and t.ma60 is not null "
                                         "and t.ma120 is not null and t.ma250 is not null "
                                         "and t.ha_open_price is not null and t.ha_close_price is not null "
                                         "and t.ha_highest_price is not null and t.ha_lowest_price is not null "
                                         "and t.bias5 is not null and t.bias10 is not null and t.bias20 is not null "
                                         "and t.bias60 is not null and t.bias120 is not null and t.bias250 is not null "
                                         "and t.ema12 is not null and t.ema26 is not null "
                                         "and t.dif is not null and t.dea is not null "
                                         "and t.rsv is not null and t.k is not null and t.d is not null "
                                         "order by t.date_ asc")

        if etf_transaction_data_list is not None and len(etf_transaction_data_list) > 0:
            for current_etf_transaction_data in etf_transaction_data_list:
                # 查询前一个交易日的记录
                date_ = DatetimeUtil.datetime_to_str(current_etf_transaction_data[1], DatetimeFormat.Date_Format_With_Line)
                sql = "select * from (select * from etf_transaction_data t where t.code_='" + code + "' and t.date_<to_date('" + date_ + "','yyyy-mm-dd') order by t.date_ desc) where rownum<=1"
                last_etf_transaction_data = self.db.execute_query(sql)[0]
                # last_etf_transaction_data = self.session.query(EtfTransactionData).filter(
                #     and_(EtfTransactionData.code_ == code,
                #          EtfTransactionData.date_ < current_etf_transaction_data[1])).order_by(
                #     EtfTransactionData.date_.desc()).first()

                # decimal转换为float
                date_list.append(last_etf_transaction_data[1])
                open_price_list.append(float(last_etf_transaction_data[3]) if last_etf_transaction_data[3] is not None else None)
                close_price_list.append(float(last_etf_transaction_data[4]) if last_etf_transaction_data[4] is not None else None)
                highest_price_list.append(float(last_etf_transaction_data[5]) if last_etf_transaction_data[5] is not None else None)
                lowest_price_list.append(float(last_etf_transaction_data[6]) if last_etf_transaction_data[6] is not None else None)
                change_amount_list.append(float(last_etf_transaction_data[8]) if last_etf_transaction_data[8] is not None else None)
                change_range_list.append(float(last_etf_transaction_data[9]) if last_etf_transaction_data[9] is not None else None)
                volume_list.append(float(last_etf_transaction_data[11]) if last_etf_transaction_data[11] is not None else None)
                turnover_list.append(float(last_etf_transaction_data[12]) if last_etf_transaction_data[12] is not None else None)
                ma5_list.append(float(last_etf_transaction_data[13]) if last_etf_transaction_data[13] is not None else None)
                ma10_list.append(float(last_etf_transaction_data[14]) if last_etf_transaction_data[14] is not None else None)
                ma20_list.append(float(last_etf_transaction_data[15]) if last_etf_transaction_data[15] is not None else None)
                ma60_list.append(float(last_etf_transaction_data[16]) if last_etf_transaction_data[16] is not None else None)
                ma120_list.append(float(last_etf_transaction_data[17]) if last_etf_transaction_data[17] is not None else None)
                ma250_list.append(float(last_etf_transaction_data[18]) if last_etf_transaction_data[18] is not None else None)
                ha_open_price_list.append(float(last_etf_transaction_data[19]) if last_etf_transaction_data[19] is not None else None)
                ha_close_price_list.append(float(last_etf_transaction_data[20]) if last_etf_transaction_data[20] is not None else None)
                ha_highest_price_list.append(float(last_etf_transaction_data[21]) if last_etf_transaction_data[21] is not None else None)
                ha_lowest_price_list.append(float(last_etf_transaction_data[22]) if last_etf_transaction_data[22] is not None else None)
                bias5_list.append(float(last_etf_transaction_data[23]) if last_etf_transaction_data[23] is not None else None)
                bias10_list.append(float(last_etf_transaction_data[24]) if last_etf_transaction_data[24] is not None else None)
                bias20_list.append(float(last_etf_transaction_data[25]) if last_etf_transaction_data[25] is not None else None)
                bias60_list.append(float(last_etf_transaction_data[26]) if last_etf_transaction_data[26] is not None else None)
                bias120_list.append(float(last_etf_transaction_data[27]) if last_etf_transaction_data[27] is not None else None)
                bias250_list.append(float(last_etf_transaction_data[28]) if last_etf_transaction_data[28] is not None else None)
                ema12_list.append(float(last_etf_transaction_data[29]) if last_etf_transaction_data[29] is not None else None)
                ema26_list.append(float(last_etf_transaction_data[30]) if last_etf_transaction_data[30] is not None else None)
                dif_list.append(float(last_etf_transaction_data[31]) if last_etf_transaction_data[31] is not None else None)
                dea_list.append(float(last_etf_transaction_data[32]) if last_etf_transaction_data[32] is not None else None)
                rsv_list.append(float(last_etf_transaction_data[33]) if last_etf_transaction_data[33] is not None else None)
                k_list.append(float(last_etf_transaction_data[34]) if last_etf_transaction_data[34] is not None else None)
                d_list.append(float(last_etf_transaction_data[35]) if last_etf_transaction_data[35] is not None else None)
                up_list.append(float(last_etf_transaction_data[36]) if last_etf_transaction_data[36] is not None else None)
                mb_list.append(float(last_etf_transaction_data[37]) if last_etf_transaction_data[37] is not None else None)
                dn_list.append(float(last_etf_transaction_data[38]) if last_etf_transaction_data[38] is not None else None)
        self.db.close()

        return (date_list,
                open_price_list,
                close_price_list,
                highest_price_list,
                lowest_price_list,
                change_amount_list,
                change_range_list,
                volume_list,
                turnover_list,
                ma5_list,
                ma10_list,
                ma20_list,
                ma60_list,
                ma120_list,
                ma250_list,
                ha_open_price_list,
                ha_close_price_list,
                ha_highest_price_list,
                ha_lowest_price_list,
                bias5_list,
                bias10_list,
                bias20_list,
                bias60_list,
                bias120_list,
                bias250_list,
                ema12_list,
                ema26_list,
                dif_list,
                dea_list,
                rsv_list,
                k_list,
                d_list,
                up_list,
                mb_list,
                dn_list,
                len(etf_transaction_data_list))
